﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core.Entity;
using System.Data;
using System.Globalization;

namespace Core.Dao
{
    class StaffDao
    {
        public bool insert(Staff staff)
        {
            object[,] param = {
                {"@UserId", staff.UserId},
                {"@Code", staff.Code},
                {"@FullName", staff.FullName},
                {"@Gender", staff.Gender},
                {"@BirthDay", staff.BirthDay},
                {"@HireDate", staff.HireDate},
                {"@Phone", staff.Phone},
                {"@Address", staff.Address},
                {"@Desc", staff.Desc},
            };
            String sql = "insert into STAFFS(USR_ID, STF_CODE, STF_FULL_NAME, STF_GENDER, STF_BIRTHDAY, STF_HIRE_DATE, STF_PHONE, STF_ADDRESS, STF_DESC) " +
                "values(@UserId, @Code, @FullName, @Gender, @BirthDay, @HireDate, @Phone, @Address, @Desc)";
            return DB.exec(sql, param);
        }

        public bool update(Staff staff)
        {
            object[,] param = {
                {"@UserId", staff.UserId},
                {"@Code", staff.Code},
                {"@FullName", staff.FullName},
                {"@Gender", staff.Gender},
                {"@BirthDay", staff.BirthDay},
                {"@HireDate", staff.HireDate},
                {"@Phone", staff.Phone},
                {"@Address", staff.Address},
                {"@Desc", staff.Desc},
                {"@id", staff.Id},
            };
            String sql = "update STAFFS set USR_ID = @UserId, STF_CODE = @Code, STF_FULL_NAME = @FullName, STF_GENDER = @Gender, STF_BIRTHDAY = @BirthDay, " +
                "STF_HIRE_DATE = @HireDate, STF_PHONE = @Phone, STF_ADDRESS = @Address, STF_DESC = @Desc where STF_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "delete from STAFFS where STF_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(String ids)
        {
            object[,] param = {
                {"@ids", ids}   
            };
            String sql = "delete from STAFFS where STF_ID in (" + ids + ")";
            return DB.exec(sql);
        }

        public DataTable get(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "select * from STAFFS where STF_ID = @id";
            return DB.getData(sql, param);
        }

        public DataTable filter(string code, string fullName, string birthDay, string hireDate,string orderBy)
        {
            object[,] param = {};
            String sql = "select STF.*, USR.USR_USERNAME, USR.USR_EMAIL from STAFFS STF, USERS USR where 1=1 ";

            if (code != "") {
                sql += "and STF.STF_CODE like @code ";
                DB.addParam(ref param, "@code", "%"+code+"%");
            }
            if (fullName != "")
            {
                sql += "and STF.STF_FULL_NAME like @fullName ";
                DB.addParam(ref param, "@fullName", "%" + fullName + "%");
            }

            try
            {
                DateTime birthday = DateTime.ParseExact(birthDay, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                sql += "and CONVERT(char(10), STF_BIRTHDAY,126) = @birthDay ";
                DB.addParam(ref param, "@birthDay", birthday.ToString("yyyy-MM-dd"));
            }
            catch(Exception ex){}

            try
            {
                DateTime hiredate = DateTime.ParseExact(hireDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                sql += "and CONVERT(char(10), STF_HIRE_DATE,126) = @birthDay ";
                DB.addParam(ref param, "@birthDay", hiredate.ToString("yyyy-MM-dd"));
            }
            catch (Exception ex) { }

            sql += "and USR.USR_ID = STF.USR_ID "+ orderBy;

            return DB.getData(sql, param);
        }

        public DataTable getAll()
        {
            String sql = "select * from STAFFS order by STF_CODE";
            return DB.getData(sql);
        }

        public object getMaxNumber()
        {
            String sql = "select max(STF_CODE) from STAFFS";
            return DB.getValue(sql);
        }


        public DataTable getStaffByUserId(int userId)
        {
            object[,] param = {
                {"@userId", userId}   
            };
            String sql = "select * from STAFFS where USR_ID = @userId";
            return DB.getData(sql, param);
        }
    }
}
